﻿using Common.AspNetCore;
using File.Api.Models;
using File.Api.Utils;
using NPOI.SS.UserModel;
using System;
using NPOI.SS.Util;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using static Common.Enums.File.FileEnum;

namespace File.Api.Service
{
    public class HeyiExcelService : CommonService
    {
        public HeyiExcelService(IServiceProvider provider) : base(provider)
        {
        }

        public void GenerateSheetHeader(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderStyle(workbook);
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            var column = 0;
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("销售订单");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.SaleCode);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("销售订单行项目");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.SaleRow);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("生产工单");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(detail.WorkCode);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("客户简称");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.CustomerName);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            column = 0;
            headerRow = sheet.CreateRow(rowNum + 1);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.IsShowNakedLamp ? "裸灯ID" : "物料ID");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.ProductCode);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.IsShowNakedLamp ? "裸灯描述" : "物料描述");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.ProductName);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("生产课别");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Format("{0}/{1}/{2}", orderModel.OrgName, orderModel.DepName, orderModel.SetionName));
            headerRow.CreateCell(column++, CellType.String).SetCellValue("生产线别");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(detail.LineName);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

            column = 0;
            headerRow = sheet.CreateRow(rowNum + 2);
            headerRow.CreateCell(0, CellType.String).SetCellValue("表单编号");
            headerRow.CreateCell(2, CellType.String).SetCellValue(versionModel?.TableCode);
            headerRow.CreateCell(4, CellType.String).SetCellValue("报表提交时间");
            headerRow.CreateCell(5, CellType.String).SetCellValue(orderModel.CreateTime.Value.ToString("yyyy-MM-dd"));
            headerRow.CreateCell(6, CellType.String).SetCellValue(reportType + "确认结果");
            headerRow.CreateCell(7, CellType.String).SetCellValue(versionModel.DecisionPolicy == 3001 ? "OK" : "NG");
            MergedRegion(sheet, rowNum + 2, rowNum + 2, 0, 1, workbook);
            MergedRegion(sheet, rowNum + 2, rowNum + 2, 2, 3, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
        }

        public void AddBatchItemHeadRow(IWorkbook workbook, ISheet sheet, int rowNum)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderStyle(workbook);
            var column = 0;
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("检验类型");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("判定结果");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("样本");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            MergedRegion(sheet, rowNum, rowNum, 1, 4, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 7, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
        }

        public void AddBatchItemDataRow(QCOrderItemModel batchItem, IWorkbook workbook, ISheet sheet, int rowNum)
        {
            var column = 0;
            IRow row = sheet.CreateRow(rowNum);
            var badRemark = string.Empty;
            batchItem.QCOrderItemBadModels.ForEach(o =>
            {
                badRemark += string.Format("{0}-{1}-{2};", o.BadName, Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel ?? 0), o.BadQty);
            });

            row.CreateCell(column++, CellType.String).SetCellValue(batchItem.CategoryName);
            row.CreateCell(column++, CellType.String).SetCellValue(batchItem.ItemName);
            row.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            row.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            row.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            row.CreateCell(column++, CellType.String).SetCellValue(batchItem.Result == 0 ? "NA" : batchItem.Result == 1 ? "OK" : "NG");
            var sampleValue = string.Empty;
            var height = Convert.ToInt16(((batchItem.ItemName.Length / 23) + 1) * 280);
            if (batchItem.QCOrderItemSamplingModels != null)
            {
                var tempData = batchItem.QCOrderItemSamplingModels.Select(item => item.Sample.ToString()).ToArray();
                var sampleHeight = 0;
                for (int i = 0; i < tempData.Length; i++)
                {
                    tempData[i] = "(" + (i + 1).ToString() + ")" + tempData[i];
                    sampleHeight += Convert.ToInt16((((tempData[i].Length + 3) / 14) + 1) * 280);
                }

                sampleValue = string.Join(";\n", tempData);
                if (!string.IsNullOrEmpty(batchItem.Answer))
                {
                    sampleHeight += Convert.ToInt16(((batchItem.Answer.Length / 14) + 1) * 280);
                    sampleValue = batchItem.Answer + ";\n" + sampleValue;
                }

                height = height < sampleHeight ? Convert.ToInt16(sampleHeight) : height;
            }

            row.Height = height;
            row.CreateCell(column++, CellType.String).SetCellValue(sampleValue);
            row.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            MergedRegion(sheet, rowNum, rowNum, 1, 4, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 7, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, row);
        }

        public void GenerateSheetFooter(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            var column = 0;
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("备注");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.Remark);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("检验员");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.CheckUserName);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            MergedRegion(sheet, rowNum, rowNum, 1, 4, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 7, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
        }

        /// <summary>
        /// 标题
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="reportType"></param>
        /// <param name="samplingSchemeName"></param>
        public void GenerateExcelOQCHeader(IWorkbook workbook, ISheet sheet, int rowNum, string orgName)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderOQCStyle(workbook);
            IRow headerRow = sheet.CreateRow(rowNum);
            if (string.IsNullOrEmpty(orgName))
            {
                headerRow.CreateCell(0, CellType.String).SetCellValue($"漳州立达信光电子科技有限公司");
            }
            else
            {
                headerRow.CreateCell(0, CellType.String).SetCellValue(orgName);
            }

            MergedRegion(sheet, 0, 0, 0, 35, workbook);
            headerRow.Height = 50 * 10;
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
            rowNum++;
            IRow headerRow1 = sheet.CreateRow(rowNum);

            headerRow1.CreateCell(0, CellType.String).SetCellValue($"OQC-出货检验报告");
            MergedRegion(sheet, 1, 1, 0, 35, workbook);
            headerRow1.Height = 50 * 10;
            headerRow1.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        /// <summary>
        /// 表头明细
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="orderModel"></param>
        public void GenerateSheetOQCHeader(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            var saleCode = string.Empty;
            if (!string.IsNullOrEmpty(orderModel.SaleCode))
            {
                saleCode = orderModel.SaleCode;
                if (!string.IsNullOrEmpty(orderModel.SaleRow))
                {
                    saleCode += orderModel.SaleRow.Replace("000", "-");
                }
            }

            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("MES检验单号");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.Code);
            headerRow.CreateCell(6, CellType.String).SetCellValue("销售订单号");
            headerRow.CreateCell(12, CellType.String).SetCellValue(saleCode);
            headerRow.CreateCell(18, CellType.String).SetCellValue("客户条码");
            headerRow.CreateCell(23, CellType.String).SetCellValue(orderModel.CustomerProductCode);
            headerRow.CreateCell(28, CellType.String).SetCellValue("抽样方法");
            headerRow.CreateCell(30, CellType.String).SetCellValue(versionModel.SamplingSchemeLevel == 0 ? "正常" : versionModel.SamplingSchemeLevel == 1 ? "放宽" : "加严");
            headerRow.CreateCell(33, CellType.String).SetCellValue("判定结论");
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 32, workbook);
            MergedRegion(sheet, rowNum, rowNum, 33, 35, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("检验日期");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.CreateTime.HasValue ? orderModel.CreateTime.Value.ToString("yyyy-MM-dd") : string.Empty);
            headerRow.CreateCell(6, CellType.String).SetCellValue("生产订单号");
            headerRow.CreateCell(12, CellType.String).SetCellValue(detail.WorkCode);
            headerRow.CreateCell(18, CellType.String).SetCellValue("ID码");
            headerRow.CreateCell(23, CellType.String).SetCellValue(orderModel.ProductCode);
            headerRow.CreateCell(28, CellType.String).SetCellValue("客户简称");
            headerRow.CreateCell(30, CellType.String).SetCellValue(orderModel.CustomerName);
            headerRow.CreateCell(33, CellType.String).SetCellValue(Enum.GetName(typeof(DecisionPolicyEnum), versionModel.DecisionPolicy));
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 32, workbook);
            if (orderModel.Type == 1)
            {
                MergedRegion(sheet, rowNum, rowNum, 33, 35, workbook);
            }

            ////MergedRegion(sheet, rowNum, rowNum, 35, 39, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("流水号");
            headerRow.CreateCell(3, CellType.String).SetCellValue(string.Join(";", orderModel.DetailModels.Select(p => p.ExtCode)));
            ////headerRow.CreateCell(28, CellType.String).SetCellValue("抽样数量");
            ////headerRow.CreateCell(30, CellType.String).SetCellValue(versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).HasValue ? versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).Value.ToString("0") : "0");
            /////headerRow.CreateCell(35, CellType.String).SetCellValue("A023");
            /////
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 32, workbook);
            if (orderModel.Type == 1)
            {
                headerRow.CreateCell(33, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 33, 35, workbook);
            }

            ////MergedRegion(sheet, rowNum, rowNum, 28, 29, workbook);
            ////MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("工单总数");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.WorkBomProductQty.Value.ToString("0"));
            headerRow.CreateCell(6, CellType.String).SetCellValue("检验批数");
            headerRow.CreateCell(12, CellType.String).SetCellValue(orderModel.DetailModels.Sum(p => p.BomProductQty) == 0 ? orderModel.DetailModels.Sum(p => p.SendQty).ToString("0") : orderModel.DetailModels.Sum(p => p.BomProductQty).ToString("0"));
            headerRow.CreateCell(18, CellType.String).SetCellValue("生产部门");
            headerRow.CreateCell(23, CellType.String).SetCellValue(orderModel.DepName);
            headerRow.CreateCell(28, CellType.String).SetCellValue("生产线别");
            headerRow.CreateCell(30, CellType.String).SetCellValue(detail.LineName);
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 32, workbook);
            if (orderModel.Type == 1)
            {
                headerRow.CreateCell(33, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 33, 35, workbook);
            }

            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue("规格型号");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.ProductName);
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 32, workbook);
            if (orderModel.Type == 1)
            {
                headerRow.CreateCell(33, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 33, 35, workbook);
            }
            else
            {
                MergedRegion(sheet, 3, rowNum, 33, 35, workbook);
            }

            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue("检验标准来源");
            headerRow.CreateCell(6, CellType.String).SetCellValue(versionModel.SamplingSchemeName);

            MergedRegion(sheet, rowNum, rowNum, 0, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 35, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        /// <summary>
        /// 定性模板
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        public void AddBatchItemOQCQualitativeRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName, int cateIndex)
        {
            var rowCount = AddBatchItemDataOQCHeaderRow(workbook, sheet, ref rowNum, items, defects, categoryName, cateIndex);
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(1, CellType.String).SetCellValue("NO");
            headerRow.CreateCell(2, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(9, CellType.String).SetCellValue("抽样数");
            headerRow.CreateCell(13, CellType.String).SetCellValue("RE");
            headerRow.CreateCell(15, CellType.String).SetCellValue("不良数");
            headerRow.CreateCell(20, CellType.String).SetCellValue("异常描述");
            headerRow.CreateCell(33, CellType.String).SetCellValue("判定");
            OQCQualitativeMergedRegion(workbook, sheet, rowNum);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

            for (var i = 1; i <= items.Count; i++)
            {
                rowNum++;
                rowCount++;
                var item = items[i - 1];
                headerRow = sheet.CreateRow(rowNum);
                var badRemark = string.Empty;
                item.QCOrderItemBadModels.ForEach(o =>
                {
                    badRemark += string.Format("{0}-{1}-{2};", o.BadName, Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel ?? 0), o.BadQty.ToString("0"));
                });

                headerRow.CreateCell(1, CellType.String).SetCellValue(i.ToString());
                headerRow.CreateCell(2, CellType.String).SetCellValue(item.ItemName);
                headerRow.CreateCell(9, CellType.String).SetCellValue(item.SampleQty.ToString("0"));
                headerRow.CreateCell(13, CellType.String).SetCellValue(item.ItemRe.ToString());
                headerRow.CreateCell(15, CellType.String).SetCellValue(item.QCOrderItemBadModels.Sum(p => p.BadQty).ToString("0"));
                headerRow.CreateCell(20, CellType.String).SetCellValue(badRemark);
                headerRow.CreateCell(33, CellType.String).SetCellValue(Enum.GetName(typeof(QcCheckResultEnum), item.Result));
                OQCQualitativeMergedRegion(workbook, sheet, rowNum);
                headerRow.Height = Convert.ToInt16(((item.ItemName.Length / 12) + 1) * 280);
                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            }

            MergedRegion(sheet, rowNum - rowCount, rowNum, 0, 0, workbook);
        }

        /// <summary>
        /// 定量模板
        /// </summary>
        /// <param name="batchItem"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        public void AddBatchItemDataOQCRationRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName, int cateIndex)
        {
            var rowCount = AddBatchItemDataOQCHeaderRow(workbook, sheet, ref rowNum, items, defects, categoryName, cateIndex);
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(1, CellType.String).SetCellValue("NO");
            headerRow.CreateCell(2, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(7, CellType.String).SetCellValue("范围值");
            headerRow.CreateCell(12, CellType.String).SetCellValue("抽样数量");
            headerRow.CreateCell(15, CellType.String).SetCellValue("测试结果(每个尺寸项目随机记录实测值)");
            MergedRegion(sheet, rowNum, rowNum, 2, 6, workbook);
            MergedRegion(sheet, rowNum, rowNum, 7, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 14, workbook);
            MergedRegion(sheet, rowNum, rowNum, 15, 35, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

            for (var i = 1; i <= items.Count; i++)
            {
                rowNum++;
                rowCount++;
                var item = items[i - 1];
                headerRow = sheet.CreateRow(rowNum);
                var rowsCount = Convert.ToInt16((item.ItemName.Length / 12) + 1);
                var height = Convert.ToInt16(((rowsCount / 2) * 280) + ((rowsCount % 2) * 140));
                height = height > 280 ? height : Convert.ToInt16(280);
                headerRow.Height = height;
                headerRow.CreateCell(1, CellType.String).SetCellValue(i.ToString());
                headerRow.CreateCell(2, CellType.String).SetCellValue(item.ItemName);
                headerRow.CreateCell(7, CellType.String).SetCellValue(MapOqcDecimalToString(item.LowerValue.Value) + "/" + MapOqcDecimalToString(item.StandardValue.Value) + "/" + MapOqcDecimalToString(item.UpperValue.Value));
                headerRow.CreateCell(12, CellType.String).SetCellValue(item.SampleQty.ToString("0"));
                headerRow.CreateCell(35, CellType.String).SetCellValue(Enum.GetName(typeof(QcCheckResultEnum), item.Result));
                int index = 15;
                ///样本值序号
                for (var j = 1; j <= 10; j++)
                {
                    headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(j.ToString());
                }

                for (var j = 1; j <= 10; j++)
                {
                    MergedRegion(sheet, rowNum, rowNum, index + ((j - 1) * 2), index + (j * 2) - 1, workbook);
                }

                ////headerRow.CreateCell(33, CellType.String).SetCellValue(Enum.GetName(typeof(QcCheckResultEnum), item.Result));
                ////headerRow.CreateCell(36, CellType.String).SetCellValue(string.Empty);

                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

                ///样本值
                rowNum++;
                rowCount++;
                headerRow = sheet.CreateRow(rowNum);
                headerRow.Height = height;
                for (var j = 1; j <= 10; j++)
                {
                    if (item.QCOrderItemSamplingModels != null && item.QCOrderItemSamplingModels.Count >= j)
                    {
                        headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(item.QCOrderItemSamplingModels[j - 1].Sample.ToString());
                    }
                    else
                    {
                        headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(string.Empty);
                    }
                }

                for (var j = 1; j <= 10; j++)
                {
                    MergedRegion(sheet, rowNum, rowNum, index + ((j - 1) * 2), index + (j * 2) - 1, workbook);
                }

                MergedRegion(sheet, rowNum - 1, rowNum, 1, 1, workbook);
                MergedRegion(sheet, rowNum - 1, rowNum, 2, 6, workbook);
                MergedRegion(sheet, rowNum - 1, rowNum, 7, 11, workbook);
                MergedRegion(sheet, rowNum - 1, rowNum, 12, 14, workbook);
                MergedRegion(sheet, rowNum - 1, rowNum, 35, 35, workbook);
                ////MergedRegion(sheet, rowNum - 1, rowNum, 36, 38, workbook);
                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            }

            MergedRegion(sheet, rowNum - rowCount, rowNum, 0, 0, workbook);
        }

        /// <summary>
        /// OQC表尾
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="orderModel"></param>
        public void GenerateSheetOQCFooter(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 3 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("备注(客户特殊要求)");
            headerRow.CreateCell(4, CellType.String).SetCellValue(versionModel.Remark);
            MergedRegion(sheet, rowNum, rowNum, 0, 3, workbook);
            MergedRegion(sheet, rowNum, rowNum, 4, 35, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue("审核");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.ExamineSealCode);
            headerRow.CreateCell(6, CellType.String).SetCellValue("检验员");
            headerRow.CreateCell(14, CellType.String).SetCellValue(versionModel.CheckUserName);
            headerRow.CreateCell(18, CellType.String).SetCellValue("表单编号");
            headerRow.CreateCell(22, CellType.String).SetCellValue(versionModel.TableCode);
            headerRow.CreateCell(28, CellType.String).SetCellValue("保存年限");
            headerRow.CreateCell(32, CellType.String).SetCellValue(versionModel.ShelfLife + "年");
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 13, workbook);
            MergedRegion(sheet, rowNum, rowNum, 14, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 21, workbook);
            MergedRegion(sheet, rowNum, rowNum, 22, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 31, workbook);
            MergedRegion(sheet, rowNum, rowNum, 32, 35, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        /// <summary>
        /// OQC设置列宽
        /// </summary>
        /// <param name="sheet"></param>
        public void GenerateSheetSetColunmsWidth(ISheet sheet)
        {
            sheet.SetColumnWidth(0, 2 * 350);
            sheet.SetColumnWidth(1, 3 * 300);
            sheet.SetColumnWidth(2, 5 * 350);
            sheet.SetColumnWidth(3, 4 * 300);
            sheet.SetColumnWidth(4, 5 * 300);
            sheet.SetColumnWidth(5, 3 * 300);
            sheet.SetColumnWidth(6, 5 * 300);
            sheet.SetColumnWidth(7, 2 * 350);
            sheet.SetColumnWidth(8, 1 * 350);
            sheet.SetColumnWidth(9, 2 * 350);
            sheet.SetColumnWidth(10, 1 * 256);

            sheet.SetColumnWidth(11, 1 * 256);
            sheet.SetColumnWidth(12, 2 * 350);
            sheet.SetColumnWidth(13, 2 * 350);

            for (int i = 14; i < 36; i++)
            {
                sheet.SetColumnWidth(i, 3 * 300);
            }

            ///sheet.SetColumnWidth(38, 16 * 300);
        }

        private string MapOqcDecimalToString(decimal value)
        {
            if (value % 1 == 0)
            {
                return value.ToString("0");
            }

            return value.ToString();
        }

        private int AddBatchItemDataOQCHeaderRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName, int cateIndex)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            var rowCount = 0;
            var defectStr = string.Empty;
            var hasDefect = false;
            var inspectionLevel = string.Empty;
            if (defects != null && defects.Any())
            {
                var tempDefects = defects.Where(p => p.DefectLevel == 0).ToList();
                tempDefects.AddRange(defects.Where(p => p.DefectLevel == 1).ToList());
                tempDefects.AddRange(defects.Where(p => p.DefectLevel == 3).ToList());
                tempDefects.AddRange(defects.Where(p => p.DefectLevel == 2).ToList());
                tempDefects.AddRange(defects.Where(p => p.DefectLevel == 4).ToList());
                var defect = defects.OrderByDescending(p => p.SampleQty).FirstOrDefault();
                tempDefects.ForEach(o =>
                {
                    defectStr += string.Format("{0}类:(AQL:{1} Ac={2} Re={3}) ; ", Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel), o.AQL, o.ItemAc, o.ItemRe);
                });

                inspectionLevel = string.Format("检验水平:{0} 抽样数:{1}PS", defect.StdName + defect.InspectionLevelCode, defect.SampleQty.HasValue ? defect.SampleQty.Value.ToString("0") : "0");
                hasDefect = true;
            }
            else
            {
                var item = items.OrderByDescending(p => p.SampleQty).FirstOrDefault();
                inspectionLevel = string.Format("抽样数量:{1} Ac={2} Re={3} 不合格数:{0} ; ", items.Where(p => p.Result == 2).Count().ToString("0"), item.SampleQty.ToString("0"), item.ItemAc, item.ItemRe);
            }

            if (categoryName.Trim() == "外观检验")
            {
                inspectionLevel = string.Format("{0}(包含包装和产品)", inspectionLevel);
            }

            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue(categoryName);

            headerRow.CreateCell(1, CellType.String).SetCellValue(inspectionLevel);
            headerRow.CreateCell(29, CellType.String).SetCellValue(string.Format("结论：{0}", items.Any(p => p.Result == 2) ? "NG" : "OK"));
            MergedRegion(sheet, rowNum, rowNum, 1, 28, workbook);
            MergedRegion(sheet, rowNum, rowNum, 29, 35, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            headerRow.Cells[1].CellStyle.Alignment = HorizontalAlignment.Justify;

            if (hasDefect && cateIndex == 0)
            {
                ///大类缺陷等级
                rowCount++;
                rowNum++;
                headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(1, CellType.String).SetCellValue(defectStr);
                MergedRegion(sheet, rowNum, rowNum, 1, 35, workbook);
                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });
            }

            rowNum++;
            rowCount++;
            return rowCount;
        }

        private void OQCQualitativeMergedRegion(IWorkbook workbook, ISheet sheet, int rowNum)
        {
            MergedRegion(sheet, rowNum, rowNum, 2, 8, workbook);
            MergedRegion(sheet, rowNum, rowNum, 9, 12, workbook);
            MergedRegion(sheet, rowNum, rowNum, 13, 14, workbook);
            MergedRegion(sheet, rowNum, rowNum, 15, 19, workbook);
            MergedRegion(sheet, rowNum, rowNum, 20, 32, workbook);
            MergedRegion(sheet, rowNum, rowNum, 33, 35, workbook);
        }

        private void MergedRegion(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, IWorkbook workbook)
        {
            var region = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.AddMergedRegion(region);
            RegionUtil.SetBorderLeft(1, region, sheet);
            RegionUtil.SetBorderRight(1, region, sheet);
            RegionUtil.SetBorderTop(1, region, sheet);
            RegionUtil.SetBorderBottom(1, region, sheet);
        }
    }
}
